package DataBase;

import Beans.Pessoa;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.swing.JOptionPane;

public class PessoaDAO extends DAO {

    public ArrayList<Pessoa> ListarTodos(String valor) {
        try {
            Conectar();
            String SQL = "SELECT * FROM pessoa where nome like '%" + valor + "%'  order by nome";
            pstmt = con.prepareStatement(SQL);
            rs = pstmt.executeQuery();
            Pessoa p = null;
            ArrayList<Pessoa> lista = new ArrayList<Pessoa>();
            while (rs.next()) {
                p = new Pessoa();
                p.setCpf(rs.getString("cpf"));
                p.setNome(rs.getString("nome"));
                p.setEndereco(rs.getString("endereco"));
                p.setTel1(rs.getString("tel1"));
                p.setTel2(rs.getString("tel2"));
                lista.add(p);
            }
            Desconectar();
            return lista;
        } catch (SQLException ex) {

            return new ArrayList<Pessoa>();
        }


    }

    public Pessoa pesquisa(String cpf) {
        try {
            Conectar();
            String SQL = "SELECT * FROM pessoa where cpf=?";
            pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, cpf);
            rs = pstmt.executeQuery();
            Pessoa p = null;
            if (rs.next()) {

                p = new Pessoa();
                p.setCpf(rs.getString("cpf"));
                p.setNome(rs.getString("nome"));
                p.setEndereco(rs.getString("endereco"));
                p.setTel1(rs.getString("tel1"));
                p.setTel2(rs.getString("tel2"));

                Desconectar();
            }else{
              p = new Pessoa();
              p.setCpf("invalido");
              return p;
            }
            return p;
        } catch (SQLException sqlex) {
            System.out.println("SQLException" + sqlex);
            return null;
        }

    }

    public boolean inserirPessoa(Pessoa p) {
        try {
            Conectar();
            String SQL = "select*from pessoa where cpf=?";
            pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, p.getCpf());
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return true;
            } else {
                SQL = "insert into pessoa (cpf,nome,endereco,tel1,tel2)values(?,?,?,?,?)";
                pstmt = con.prepareStatement(SQL);
                pstmt.setString(1, p.getCpf());
                pstmt.setString(2, p.getNome());
                pstmt.setString(3, p.getEndereco());
                pstmt.setString(4, p.getTel1());
                pstmt.setString(5, p.getTel2());
                pstmt.executeUpdate();

                return true;
            }
        } catch (SQLException sqlex) {
            return false;
        } finally {
            Desconectar();
        }
    }

    public boolean Alterar(Pessoa p) {
        try {
            Conectar();
            String SQL = "update pessoa set nome=?, endereco=?, tel1=?, tel2=? where cpf=?";
            pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, p.getNome());
            pstmt.setString(2, p.getEndereco());
            pstmt.setString(3, p.getTel1());
            pstmt.setString(4, p.getTel2());
            pstmt.setString(5, p.getCpf());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException sqlex) {
            return false;
        } finally {
            Desconectar();
        }


    }

    public boolean verificarPessoa(String cpf) {
        try {
            Conectar();
            String SQL = "select*from pessoa where cpf=?";
            pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, cpf);
            rs = pstmt.executeQuery();
            Desconectar();
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqlex) {
            return false;

        }
    }
    

    public String[] getNomes() {
        try {
            Conectar();
            String SQL = ("select count(*) as quant from pessoa ");
            pstmt = con.prepareStatement(SQL);
            rs = pstmt.executeQuery();
            int quant = 0;
            if (rs.next()) {
                quant = rs.getInt("quant");
            }
            String[] nomes = new String[quant];
            int i = 0;
            rs = null;
            SQL = ("select nome from pessoa order by nome;");
            pstmt = con.prepareStatement(SQL);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                nomes[i] = rs.getString("nome");
                i++;
            }
            Desconectar();
            return nomes;
        } catch (SQLException sqlex) {
            System.out.println("Execeção getNomes" + sqlex.getMessage());
            return new String[1];
        }
    }
}
